﻿Imports ZViewTVDotNet.SQLiteWrapper
Imports System.Xml
Imports System.Text
Imports System.IO
Imports System.Text.RegularExpressions

Module Database

#Region "Base"
    Public Sub CreateDataBase()

        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        db.ExecuteNonQueryFast("PRAGMA synchronous=0;")
        db.ExecuteNonQueryFast("PRAGMA temp_store = 2;")
        db.ExecuteNonQueryFast("PRAGMA Journal_mode = memory;")

        db.ExecuteNonQueryFast("CREATE Table PlayList " & _
                "(Id INTEGER PRIMARY KEY ASC, " & _
                "Name TEXT," & _
                "Active INTEGER default '0'); ")

        db.ExecuteNonQueryFast("CREATE Table Channel " & _
                        "(Id INTEGER PRIMARY KEY ASC, " & _
                        "ExtPlayListID INTEGER, " & _
                        "Location Text, " & _
                        "Title TEXT, " & _
                        "Extension TEXT, " & _
                        "Ordering INTEGER," & _
                        "ZGTVDotNETID TEXT); ")


        db.CloseDatabase()
        db = Nothing


    End Sub
    Private Sub CreateSavePlaylist(ByVal filepath As String)
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(filepath)
        db.ExecuteNonQueryFast("PRAGMA synchronous=0;")
        db.ExecuteNonQueryFast("PRAGMA temp_store = 2;")
        db.ExecuteNonQueryFast("PRAGMA Journal_mode = memory;")

        db.ExecuteNonQueryFast("CREATE Table PlayList " & _
                "(Name TEXT); ")

        db.ExecuteNonQueryFast("CREATE Table Channel " & _
                        "(Location Text, " & _
                        "Title TEXT, " & _
                        "Extension TEXT, " & _
                        "Ordering INTEGER); ")

        db.CloseDatabase()
        db = Nothing

    End Sub
    Public Sub DeleteDataBase()
        Try

            ' Erase old DB
            If My.Computer.FileSystem.FileExists(BDDPath & "Channels.db3") Then
                My.Computer.FileSystem.DeleteFile(BDDPath & "Channels.db3")
            End If

        Catch E As Exception
            'MessageBox.Show(E.Message & " Error in: DataBase.DeleteDB").ToString()
            Dim sql As String
            Dim db As SQLiteBase = New SQLiteBase
            db.OpenDatabase(BDDPath & "Channels.db3")

            sql = "begin transaction;"
            db.ExecuteNonQueryFast(sql)

            sql = "drop table PlayList;"
            db.ExecuteNonQueryFast(sql)

            sql = "drop table Channel;"
            db.ExecuteNonQueryFast(sql)

            sql = "end transaction;"
            db.ExecuteNonQueryFast(sql)

            db.CloseDatabase()
            db = Nothing
        End Try

    End Sub

#End Region

#Region "Import/Export"
    Public Sub ImportConfVDR(ByVal filepath As String, ByVal titre As String)

        Dim confoptionsVDR() As dvboption = {New dvboption("I0", "dvb-inversion=0"), _
                                         New dvboption("I1", "dvb-inversion=1"), _
                                         New dvboption("I999", "dvb-inversion=2"), _
                                         New dvboption("B6", "dvb-bandwidth=6"), _
                                         New dvboption("B7", "dvb-bandwidth=7"), _
                                         New dvboption("B8", "dvb-bandwidth=8"), _
                                         New dvboption("B999", "dvb-bandwidth=0"), _
                                         New dvboption("C0", "dvb-fec=0"), _
                                         New dvboption("C12", "dvb-fec=1"), _
                                         New dvboption("C23", "dvb-fec=2"), _
                                         New dvboption("C34", "dvb-fec=3"), _
                                         New dvboption("C45", "dvb-fec=4"), _
                                         New dvboption("C56", "dvb-fec=5"), _
                                         New dvboption("C67", "dvb-fec=6"), _
                                         New dvboption("C78", "dvb-fec=7"), _
                                         New dvboption("C89", "dvb-fec=8"), _
                                         New dvboption("C999", "dvb-fec=9"), _
                                         New dvboption("D0", "dvb-fec=0"), _
                                         New dvboption("D12", "dvb-fec=1"), _
                                         New dvboption("D23", "dvb-fec=2"), _
                                         New dvboption("D34", "dvb-fec=3"), _
                                         New dvboption("D45", "dvb-fec=4"), _
                                         New dvboption("D56", "dvb-fec=5"), _
                                         New dvboption("D67", "dvb-fec=6"), _
                                         New dvboption("D78", "dvb-fec=7"), _
                                         New dvboption("D89", "dvb-fec=8"), _
                                         New dvboption("D999", "dvb-fec=9"), _
                                         New dvboption("G4", "dvb-guard=4"), _
                                         New dvboption("G8", "dvb-guard=8"), _
                                         New dvboption("G16", "dvb-guard=16"), _
                                         New dvboption("G2", "dvb-guard=32"), _
                                         New dvboption("Y0", "dvb-hierarchy=-1"), _
                                         New dvboption("Y1", "dvb-hierarchy=1"), _
                                         New dvboption("Y2", "dvb-hierarchy=2"), _
                                         New dvboption("Y4", "dvb-hierarchy=4"), _
                                         New dvboption("QPSK", "dvb-modulation=-1"), _
                                         New dvboption("M999", "dvb-modulation=0"), _
                                         New dvboption("M16", "dvb-modulation=16"), _
                                         New dvboption("M32", "dvb-modulation=32"), _
                                         New dvboption("M64", "dvb-modulation=64"), _
                                         New dvboption("M128", "dvb-modulation=128"), _
                                         New dvboption("M256", "dvb-modulation=256"), _
                                         New dvboption("0", "0")}
        Dim sr As New StreamReader(filepath)
        Dim strLine As String
        Dim ordering As Integer = 1
        Dim IDPlayList As Int64 = InsertPlaylist(titre.Substring(0, titre.LastIndexOf(".")))
        While Not (sr.EndOfStream)
            strLine = sr.ReadLine
            If strLine.Length > 0 Then
                Dim firstChar As String = strLine.Substring(0, 1)

                If Not (firstChar.Equals(" ") OrElse _
                        firstChar.Equals(Convert.ToChar(10)) OrElse _
                        firstChar.Equals(Convert.ToChar(11)) OrElse _
                        firstChar.Equals(Convert.ToChar(9)) OrElse _
                        firstChar.Equals("#")) Then

                    Dim chaines() As String = strLine.Split(":"c)
                    Dim title As String
                    If chaines(0).IndexOf(";"c) > 0 Then
                        title = chaines(0).Substring(0, chaines(0).IndexOf(";"c))
                    Else
                        title = chaines(0)
                    End If

                    Dim frequence As String = chaines(1)
                    If frequence.Trim.Length < 6 Then frequence = frequence & "000"
                    Dim extension As String = ":dvb-adapter=0:dvb-frequency=" & frequence
                    Dim regexp As Regex = New Regex("[A-Z]\d{1,3}")

                    Dim colMatch As MatchCollection = regexp.Matches(chaines(2))
                    For Each OneMatch As Match In colMatch
                        For u As Integer = 0 To confoptionsVDR.Length - 1
                            If OneMatch.Value.Equals(confoptionsVDR(u).name) Then
                                extension = extension & ":" & confoptionsVDR(u).options
                                Exit For
                            End If
                        Next
                    Next
                    Dim location As String = ""
                    Select Case chaines(3).Substring(0, 1)
                        Case "T"
                            location = "dvb-t://"
                        Case "C"
                            location = "dvb-c://"
                            extension = extension & ":" & "dvb-srate=" & chaines(4)
                        Case "S"
                            location = "dvb-s://"
                            extension = extension & ":" & "dvb-srate=" & chaines(4)
                    End Select

                    extension = extension & ":" & "program=" & chaines(9)

                    InsertChannel(IDPlayList, location, title, extension, ordering)

                    ordering += 1




                End If

            End If
        End While

    End Sub

    Private Structure dvboption
        Public name As String
        Public options As String
        Public Sub New(ByVal nom As String, ByVal opt As String)
            Me.name = nom
            Me.options = opt
        End Sub
    End Structure

    Public Sub ImportConf(ByVal filepath As String, ByVal Titre As String)

        Dim confoptions() As dvboption = {New dvboption("INVERSION_OFF", "dvb-inversion=0"), _
                                          New dvboption("INVERSION_ON", "dvb-inversion=1"), _
                                          New dvboption("INVERSION_AUTO", "dvb-inversion=2"), _
                                          New dvboption("BANDWIDTH_AUTO", "dvb-bandwidth=0"), _
                                          New dvboption("BANDWIDTH_6_MHZ", "dvb-bandwidth=6"), _
                                          New dvboption("BANDWIDTH_7_MHZ", "dvb-bandwidth=7"), _
                                          New dvboption("BANDWIDTH_8_MHZ", "dvb-bandwidth=8"), _
                                          New dvboption("FEC_NONE", "dvb-fec=0"), _
                                          New dvboption("FEC_1_2", "dvb-fec=1"), _
                                          New dvboption("FEC_2_3", "dvb-fec=2"), _
                                          New dvboption("FEC_3_4", "dvb-fec=3"), _
                                          New dvboption("FEC_4_5", "dvb-fec=4"), _
                                          New dvboption("FEC_5_6", "dvb-fec=5"), _
                                          New dvboption("FEC_6_7", "dvb-fec=6"), _
                                          New dvboption("FEC_7_8", "dvb-fec=7"), _
                                          New dvboption("FEC_8_9", "dvb-fec=8"), _
                                          New dvboption("FEC_AUTO", "dvb-fec=9"), _
                                          New dvboption("GUARD_INTERVAL_AUTO", "dvb-guard=0"), _
                                          New dvboption("GUARD_INTERVAL_1_4", "dvb-guard=4"), _
                                          New dvboption("GUARD_INTERVAL_1_8", "dvb-guard=8"), _
                                          New dvboption("GUARD_INTERVAL_1_16", "dvb-guard=16"), _
                                          New dvboption("GUARD_INTERVAL_1_32", "dvb-guard=32"), _
                                          New dvboption("HIERARCHY_NONE", "dvb-hierarchy=-1"), _
                                          New dvboption("HIERARCHY_1", "dvb-hierarchy=1"), _
                                          New dvboption("HIERARCHY_2", "dvb-hierarchy=2"), _
                                          New dvboption("HIERARCHY_4", "dvb-hierarchy=4"), _
                                          New dvboption("QPSK", "dvb-modulation=-1"), _
                                          New dvboption("QAM_AUTO", "dvb-modulation=0"), _
                                          New dvboption("QAM_16", "dvb-modulation=16"), _
                                          New dvboption("QAM_32", "dvb-modulation=32"), _
                                          New dvboption("QAM_64", "dvb-modulation=64"), _
                                          New dvboption("QAM_128", "dvb-modulation=128"), _
                                          New dvboption("QAM_256", "dvb-modulation=256"), _
                                          New dvboption("8VSB", "dvb-modulation=8"), _
                                          New dvboption("16VSB", "dvb-modulation=16"), _
                                          New dvboption("TRANSMISSION_MODE_AUTO", "dvb-transmission=0"), _
                                          New dvboption("TRANSMISSION_MODE_2K", "dvb-transmission=2"), _
                                          New dvboption("TRANSMISSION_MODE_8K", "dvb-transmission=8"), _
                                          New dvboption("0", "0")}

        Dim sr As New StreamReader(filepath)
        Dim strLine As String
        Dim ordering As Integer = 1
        Dim IDPlayList As Int64 = InsertPlaylist(Titre.Substring(0, Titre.LastIndexOf(".")))

        While Not (sr.EndOfStream)
            strLine = sr.ReadLine
            If strLine.Length > 0 Then
                Dim firstChar As String = strLine.Substring(0, 1)

                If Not (firstChar.Equals(" ") OrElse _
                        firstChar.Equals(Convert.ToChar(10)) OrElse _
                        firstChar.Equals(Convert.ToChar(11)) OrElse _
                        firstChar.Equals(Convert.ToChar(9)) OrElse _
                        firstChar.Equals("#")) Then

                    Dim chaines() As String = strLine.Split(":"c)
                    Dim location As String = ""

                    Select Case chaines.Length
                        Case 13
                            location = "dvb-t://"
                        Case 9
                            location = "dvb-c://"
                        Case 8
                            location = "dvb-s://"
                    End Select

                    Dim title As String = chaines(0)
                    Dim frequence As String = chaines(1)
                    Dim extension As String = ":dvb-adapter=0:dvb-frequency=" & frequence
                    For i As Integer = 2 To chaines.Length - 1
                        Dim test As Boolean = False
                        For u As Integer = 0 To confoptions.Length - 1
                            If chaines(i).Equals(confoptions(u).name) Then
                                extension = extension & ":" & confoptions(u).options
                                test = True
                                Exit For
                            End If
                        Next
                        Dim bidon As Long
                        If (Not (test)) Then
                            If Long.TryParse(chaines(i), bidon) Then
                                If i = 4 Then
                                    extension = extension & ":" & "dvb-srate=" & chaines(i)
                                ElseIf i = chaines.Length - 1 Then
                                    extension = extension & ":" & "program=" & chaines(i)
                                End If
                            End If

                        End If

                    Next
                    InsertChannel(IDPlayList, location, title, extension, ordering)
                    ordering += 1
                End If
            End If
        End While
    End Sub




    Public Sub ImportXSPF(ByVal filepath As String, ByVal Titre As String)
        Dim IDPlayList As Int64 = InsertPlaylist(Titre.Substring(0, Titre.LastIndexOf(".")))
        Dim extension As New StringBuilder
        Dim ordering As Integer = 1
        Dim XSPFFile As New XmlDocument
        XSPFFile.Load(filepath)
        Dim ChannelList As XmlNodeList
        ChannelList = XSPFFile.DocumentElement.GetElementsByTagName("track")
        For Each channel As XmlNode In ChannelList
            Dim location As String = channel.FirstChild.InnerText
            Dim title As String = channel.ChildNodes(1).InnerText

            Dim protocole As String
            protocole = location.Remove(location.IndexOf(":"))
            Select Case protocole
                Case "dvb-t", "dvb-c", "dvb-s", "atsc"
                    Dim options As XmlNodeList
                    options = channel.ChildNodes(2).ChildNodes
                    For Each opt As XmlNode In options
                        If opt.Name.Equals("vlc:option") Then
                            extension.Append(":" & opt.InnerText.Trim)
                        End If
                    Next
                Case "rtp", "rstp", "http", "https", "mms", "file"
                    extension.Clear()
                Case Else
                    extension.Clear()
            End Select
            InsertChannel(IDPlayList, location, title, extension.ToString, ordering)
            ordering += 1
            extension.Clear()
        Next
    End Sub
    Public Sub ImportM3U(ByVal filepath As String, ByVal titre As String)
        Dim sr As New StreamReader(filepath)
        Dim strLine As String
        Dim IDPlayList As Int64 = InsertPlaylist(titre.Substring(0, titre.LastIndexOf(".")))
        Dim extension As New StringBuilder
        Dim ordering As Integer = 1
        Dim isParsing As Boolean = False
        Dim location As String
        Dim title As String
        While Not (sr.EndOfStream)
            strLine = sr.ReadLine
            If strLine.StartsWith("#") Then
                Dim linetype() As String = strLine.Split(CChar(":"))
                If linetype(0).Equals("#EXTINF") Then
                    title = linetype(1).Substring(linetype(1).IndexOf(",") + 1)
                    isParsing = True
                    While isParsing
                        strLine = sr.ReadLine
                        If strLine.StartsWith("#EXTVLCOPT") Then
                            extension.Append(strLine.Substring(strLine.IndexOf(":")))
                        Else
                            location = strLine
                            isParsing = False
                            InsertChannel(IDPlayList, location, title, extension.ToString, ordering)
                            ordering += 1
                            extension.Clear()

                        End If
                    End While

                End If

            End If
        End While
    End Sub
    Public Sub ImportPTVMod(ByVal filepath As String)
        Dim IDPlayList As Int64 = InsertPlaylist("PouchinTVMod")
        Dim extension As New StringBuilder
        Dim ordering As Integer = 1
        Dim location As String = "dvb-t://"
        Dim Title As String = ""
        Dim ptvFile As New XmlDocument
        ptvFile.Load(filepath)
        Dim ChannelList As XmlNodeList
        ChannelList = ptvFile.DocumentElement.GetElementsByTagName("Chaine")
        For Each channel As XmlNode In ChannelList
            extension.Append(":dvb-adapter=0:dvb-bandwidth=8")
            For Each param As XmlNode In channel
                Select Case param.Name
                    Case "Nom"
                        Title = param.InnerText
                    Case "Frequence"
                        extension.Append(String.Concat(":dvb-frequency=" & param.InnerText & "000"))
                    Case "SID"
                        extension.Append(String.Concat(":program=" & param.InnerText))
                End Select
            Next
            InsertChannel(IDPlayList, location, Title, extension.ToString, ordering)
            ordering += 1
            extension.Clear()
        Next
    End Sub
    Public Sub ExportSQLite(ByVal filepath As String, ByVal id As String)
        CreateSavePlaylist(filepath)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(filepath)
        db.AttachDatabase(BDDPath & "Channels.db3", "original")
        sql = "insert into playlist select name from original.playlist where id=" & id
        db.ExecuteNonQueryFast(sql)
        sql = "insert into channel select Location, Title, Extension,Ordering from original.channel where ExtPlaylistId=" & id
        db.ExecuteNonQueryFast(sql)
        db.DetachDatabase("original")
        db.CloseDatabase()
        db = Nothing
    End Sub
    Public Sub ImportSQLite(ByVal filepath As String)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        db.AttachDatabase(filepath, "Sauvegarde")
        sql = "insert into playlist select null, name, 0 from sauvegarde.playlist"
        db.ExecuteNonQueryFast(sql)
        Dim last As Int64 = db.LastInsert
        sql = "insert into channel select null, " & last.ToString & ", Location, Title, Extension, Ordering, '' from sauvegarde.channel"
        db.ExecuteNonQueryFast(sql)
        db.DetachDatabase("Sauvegarde")
        db.CloseDatabase()
        db = Nothing

    End Sub

#End Region

    Private _ActivePlaylist As Int64
    Public Function InsertPlaylist(ByVal Name As String) As Int64
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        sql = "Insert into PlayList (name) Values ('" & Name & "')"
        db.ExecuteNonQueryFast(sql)
        Dim last As Int64 = db.LastInsert
        db.CloseDatabase()
        db = Nothing
        Return last
    End Function

    Public Sub DeletePlaylist(ByVal id As Int64)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        sql = "delete from playlist where id=" & id.ToString
        db.ExecuteNonQueryFast(sql)
        sql = "delete from channel where ExtPlayListid=" & id
        db.ExecuteNonQueryFast(sql)
        db.CloseDatabase()
        db = Nothing

    End Sub

    Public Sub InsertChannel(ByVal extPlaylist As Int64, ByVal Location As String, ByVal Title As String, ByVal Extension As String, ByVal Ordering As Integer)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        sql = "insert into Channel (ExtPlayListID, Location, Title, Extension, Ordering) values (" & _
            extPlaylist.ToString & ", '" & Location & "', '" & Title & "', '" & Extension & "', " & Ordering & " );"
        db.ExecuteNonQueryFast(sql)
        db.CloseDatabase()
        db = Nothing
    End Sub

    Public Sub initDatabase(ByVal IDChaine As Int64)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        If IDChaine = 0 Then
            sql = "select id from playlist where active=1"
        Else
            sql = " select extPlaylistID from channel where id=" & IDChaine.ToString
        End If
        Dim dtActive As DataTable = db.ExecuteQuery(sql)
        If dtActive.Rows.Count <> 0 Then
            _ActivePlaylist = CLng(dtActive.Rows(0).Item(0))
        Else
            _ActivePlaylist = FirstPlaylist()
        End If
        db.CloseDatabase()
        db = Nothing


    End Sub

    Public Sub UpdateChannelOrdering(ByVal ordering As Integer, ByVal ID As Integer)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        sql = "UPDATE Channel SET Ordering=" & ordering.ToString & " WHERE ID ='" & ID.ToString & "';"
        db.ExecuteNonQueryFast(sql)
        db.CloseDatabase()
        db = Nothing

    End Sub

    Public Sub UpdateChannelName(ByVal Name As String, ByVal id As Integer)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        sql = "UPDATE Channel SET Title='" & Name & "' WHERE ID ='" & id.ToString & "';"
        db.ExecuteNonQueryFast(sql)
        db.CloseDatabase()
        db = Nothing

    End Sub

    Public Sub DeleteChannel(ByVal NotIn As String, ByVal playlist As Integer)
        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        sql = "DELETE FROM Channel WHERE extPlaylistID=" & playlist.ToString & " AND ID NOT IN" & NotIn
        db.ExecuteNonQueryFast(sql)
        db.CloseDatabase()
        db = Nothing

    End Sub

    Public Property ActivePlaylist As Int64
        Get
            Return _ActivePlaylist
        End Get
        Set(ByVal value As Int64)
            Dim sql As String
            Dim db As SQLiteBase = New SQLiteBase
            db.OpenDatabase(BDDPath & "Channels.db3")
            sql = "update PlayList set Active='0'"
            db.ExecuteNonQueryFast(sql)
            sql = "update playlist set Active=1 where id=" & value
            db.ExecuteNonQueryFast(sql)
            db.CloseDatabase()
            db = Nothing
            _ActivePlaylist = value
        End Set
    End Property

    Private Function FirstPlaylist() As Int64

        Dim sql As String
        Dim db As SQLiteBase = New SQLiteBase
        db.OpenDatabase(BDDPath & "Channels.db3")
        sql = "select min(id) from playlist where active=1"
        Dim dtFirstPlaylist As DataTable = db.ExecuteQuery(sql)
        If dtFirstPlaylist.Rows.Count > 0 AndAlso dtFirstPlaylist.Rows(0).Item(0).ToString.Length > 0 Then
            Return CLng(dtFirstPlaylist.Rows(0).Item(0))
        Else
            Return 0
        End If
    End Function

End Module
